1 Imports System.Data.SqlClient
2 Public Class frmDiscount
3 Sub fillSession()
4 Try
5 con = New SqlConnection(cs)
6 con.Open()
7 adp = New sqlDataAdapter()
8 adp.SelectCommand = New SqlCommand("SELECT distinct (session) FROM Student", con)
9 ds = New DataSet("ds")
10 adp.Fill(ds)
11 dtable = ds.Tables(0)
12 cmbSession.Items.Clear()
13 For Each drow As DataRow In dtable.Rows
14 cmbSession.Items.Add(drow(0).ToString())
15 Next
16 Catch ex As Exception
17 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
18 End Try
19 End Sub
20 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
21 Me.Close()
22 End Sub
23 Sub Reset()
24 cmbClass.SelectedIndex = -1
25 cmbSection.SelectedIndex = -1
26 cmbSession.SelectedIndex = -1
27 cmbFeeType.SelectedIndex = -1
28 dgw.Rows.Clear()
29 cmbClass.Enabled = False
30 cmbSection.Enabled = False
31 btnUpdate.Enabled = False
32 End Sub
33 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
34 Reset()
35 End Sub
36
37 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
38 Try
39 If MessageBox.Show("Do you really want to update the records?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Information) = Windows.Forms.DialogResult.Yes Then
40 update_records()
41 End If
42 Catch ex As Exception
43 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
44 End Try
45 End Sub
46 Sub update_records()
47 Try
48 If Len(Trim(cmbFeeType.Text)) = 0 Then
49 MessageBox.Show("Please select fee type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
50 cmbFeeType.Focus()
51 Exit Sub
52 End If
53 If dgw.Rows.Count = 0 Then
54 MessageBox.Show("Sorry nothing to update.." & vbCrLf & "Please retrieve data in datagridview", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
55 Exit Sub
56 End If
57 con = New SqlConnection(cs)
58 con.Open()
59 Dim cb As String = "delete from discount where admissionNo=@d1 and FeeType=@d2"
60 cmd = New SqlCommand(cb)
61 cmd.Connection = con
62
63 ' Add Parameters to Command Parameters collection
64 cmd.Parameters.Add(New SqlParameter("@d1", System.Data.SqlDbType.NChar, 15, "AdmissionNo"))
65
66 cmd.Parameters.Add(New SqlParameter("@d2", System.Data.SqlDbType.NChar, 50, "FeeType"))
67
68
69 ' Prepare command for repeated execution
70 cmd.Prepare()
71
72 ' Data to be inserted
73 For Each row As DataGridViewRow In dgw.Rows
74 If Not row.IsNewRow Then
75
76 cmd.Parameters("@d1").Value = row.Cells(0).Value.ToString
77 cmd.Parameters("@d2").Value = cmbFeeType.Text
78 cmd.ExecuteNonQuery()
79 End If
80 Next
81 con.Close()
82 con = New SqlConnection(cs)
83 con.Open()
84 Dim cb1 As String = "insert into Discount(AdmissionNo,FeeType,Discount) VALUES (@d1,@d2,@d3)"
85 cmd = New SqlCommand(cb1)
86 cmd.Connection = con
87
88
89
90 ' Add Parameters to Command Parameters collection
91 cmd.Parameters.Add(New SqlParameter("@d1", System.Data.SqlDbType.NChar, 15, "AdmissionNo"))
92
93 cmd.Parameters.Add(New SqlParameter("@d2", System.Data.SqlDbType.NChar, 50, "FeeType"))
94
95 cmd.Parameters.Add(New SqlParameter("@d3", System.Data.SqlDbType.Float, 10, "Discount"))
96
97
98 ' Prepare command for repeated execution
99 cmd.Prepare()
100
101 ' Data to be inserted
102 For Each row As DataGridViewRow In dgw.Rows
103 If Not row.IsNewRow Then
104
105 cmd.Parameters("@d1").Value = row.Cells(0).Value.ToString
106 cmd.Parameters("@d2").Value = cmbFeeType.Text
107 cmd.Parameters("@d3").Value = CDbl(row.Cells(3).Value.ToString)
108 cmd.ExecuteNonQuery()
109 End If
110 Next
111 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
112 btnUpdate.Enabled = False
113 con.Close()
114 Catch ex As Exception
115 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
116 End Try
117 End Sub
118
119 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
120 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
121 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
122 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
123 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
124 End If
125 Dim b As Brush = SystemBrushes.ControlText
126 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
127
128 End Sub
129
130 Private Sub frmFeeName_Load_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
131 fillSession()
132 End Sub
133
134 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
135 Try
136 cmbClass.Enabled = True
137 con = New SqlConnection(cs)
138 con.Open()
139 Dim ct As String = "SELECT distinct RTRIM(ClassName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and session=@d1"
140 cmd = New SqlCommand(ct)
141 cmd.Connection = con
142 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
143 rdr = cmd.ExecuteReader()
144 cmbClass.Items.Clear()
145 While rdr.Read
146 cmbClass.Items.Add(rdr(0))
147 End While
148 con.Close()
149 Catch ex As Exception
150 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
151 End Try
152 End Sub
153
154 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
155 Try
156 cmbSection.Enabled = True
157 con = New SqlConnection(cs)
158 con.Open()
159 Dim ct As String = "SELECT distinct RTRIM(SectionName) FROM Student,Section,Class where Student.SectionID=Section.ID and Section.Class=Class.ClassName and session=@d1 and ClassName=@d2"
160 cmd = New SqlCommand(ct)
161 cmd.Connection = con
162 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
163 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
164 rdr = cmd.ExecuteReader()
165 cmbSection.Items.Clear()
166 While rdr.Read
167 cmbSection.Items.Add(rdr(0))
168 End While
169 con.Close()
170 Catch ex As Exception
171 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
172 End Try
173 End Sub
174
175
176 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
177 Try
178 If Len(Trim(cmbSession.Text)) = 0 Then
179 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
180 cmbSession.Focus()
181 Exit Sub
182 End If
183 If Len(Trim(cmbClass.Text)) = 0 Then
184 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
185 cmbClass.Focus()
186 Exit Sub
187 End If
188 If Len(Trim(cmbSection.Text)) = 0 Then
189 MessageBox.Show("Please select section", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
190 cmbSection.Focus()
191 Exit Sub
192 End If
193 If Len(Trim(cmbFeeType.Text)) = 0 Then
194 MessageBox.Show("Please select fee type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
195 cmbFeeType.Focus()
196 Exit Sub
197 End If
198 con = New SqlConnection(cs)
199 con.Open()
200 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(StudentName),RTRIM(Caste),RTRIM(Discount) from Student,Class,Section,SchoolInfo,Discount where Student.SectionID=Section.ID and Class.Classname=Section.Class and SchoolInfo.S_ID=Student.SchoolID and Discount.AdmissionNo=Student.AdmissionNo and SectionName=@d1 and ClassName=@d2 and Session=@d3 and FeeType='" & cmbFeeType.Text & "' and Status='Active' order by StudentName", con)
201 cmd.Parameters.AddWithValue("@d1", cmbSection.Text)
202 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
203 cmd.Parameters.AddWithValue("@d3", cmbSession.Text)
204 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
205 dgw.Rows.Clear()
206 While (rdr.Read() = True)
207 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
208 End While
209 con.Close()
210 btnUpdate.Enabled = True
211 Catch ex As Exception
212 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
213 End Try
214 End Sub
215
216 End Class
